﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace QT.SEO
{
    /// <summary>
    /// 关键字操作类
    /// </summary>
    public class Keyword
    {
         ///<summary>
         ///获取所有URL的关键字查询结果
         ///</summary>
         ///<returns>DataTable</returns>
        public static DataTable GetAll()
        {
            string sql = "select a.[id],a.[keyword],a.[rank],a.[snapdate],a.[checkdate],b.[SearchName] from [QT_Data_KeywordRank] a left join [QT_Basic_SearchEngine] b on a.[sid]=b.[sid] order by a.[uid],a.[keyword],a.[sid] asc";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);
            return dt;
        }

        /// <summary>
        /// 获取基础关键字列表
        /// </summary>
        /// <returns></returns>
        public static DataTable GetKeywords()
        {
            string sql = "select [guid],[keyword] from [QT_Basic_Keyword] order by [keyword] asc";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);
            return dt;
        }

        /// <summary>
        /// 获取指定URL的关键字列表
        /// </summary>
        /// <param name="uid">网址ID</param>
        /// <returns>DataTable</returns>
        public static DataTable Get(string uid)
        {
            string sql = "select a.[id],a.[keyword],a.[rank],a.[snapdate],a.[checkdate],b.[SearchName] from [QT_Data_KeywordRank] a left join [QT_Basic_SearchEngine] b on a.[sid]=b.[sid] where a.[uid]='" + uid + "' order by a.[uid],a.[keyword],a.[sid] asc";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);
            return dt;
        }

        /// <summary>
        /// 获取指定URL的关键字列表
        /// </summary>
        /// <param name="uid">网址ID</param>
        /// <param name="keyword">关键字</param>
        /// <returns>DataTable</returns>
        public static DataTable Get(string uid,string keyword)
        {
            string sql = "select a.[id],a.[keyword],a.[rank],a.[snapdate],a.[checkdate],b.[SearchName] from [QT_Data_KeywordRank] a left join [QT_Basic_SearchEngine] b on a.[sid]=b.[sid] where a.[uid]='" + uid + "' and a.[keyword]='" + keyword + "' order by a.[uid],a.[keyword],a.[sid] asc";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);
            return dt;
        }

        /// <summary>
        /// 获取指定分组的基础关键字
        /// </summary>
        /// <param name="gid"></param>
        /// <returns></returns>
        public static DataTable GetKeywords(string gid)
        {
            string sql = "select [guid],[keyword] from [QT_Basic_Keyword]" + (string.IsNullOrEmpty(gid) ? "" : (" where [gid]='" + gid + "'"));
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);
            return dt;
        }

        /// <summary>
        /// 添加基础关键字
        /// </summary>
        /// <param name="keyword">关键字</param>
        /// <returns>int</returns>
        public static int Add(string keyword)
        {
            if (Exsits(null, keyword)) return -2;

            string sql = "insert into [QT_Basic_Keyword]([guid],[keyword]) values('" + Guid.NewGuid().ToString().Replace("-", "") + "','" + keyword + "')";
            int rval = new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);
            return rval;
        }

        /// <summary>
        /// 添加关键字查询结果
        /// </summary>
        /// <param name="uid">网址ID</param>
        /// <param name="keyword">关键字</param>
        /// <param name="id">排名</param>
        /// <param name="date">快照日期</param>
        /// <param name="sid">搜索引擎ID</param>
        /// <returns>int</returns>
        public static int Add(string uid, string keyword, string id, string date, string sid)
        {
            if (Exsits(uid, keyword, sid)) return -2;

            string sql = "insert into [QT_Data_KeywordRank]([id],[uid],[keyword],[rank],[SnapDate],[checkDate],[sid]) values('" + Guid.NewGuid().ToString().Replace("-", "") + "','" + uid + "','" + keyword + "'," + id + ",'" + date + "','" + DateTime.Now.ToString("yyyy-MM-dd") + "','" + sid + "')";
            int rval = new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);
            return rval;
        }

        /// <summary>
        /// 修改关键字
        /// </summary>
        /// <param name="guid">Guid</param>
        /// <param name="keywords">关键字</param>
        /// <returns>int</returns>
        public static int Update(string guid, string keywords)
        {
            string sql = "update [QT_Data_UrlList] set [Keywords]='" + keywords + "' where [guid]='" + guid + "'";
            int rval = new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);
            return rval;
        }

        /// <summary>
        /// 删除基础关键字
        /// </summary>
        /// <param name="guids">guid</param>
        /// <returns>int</returns>
        public static int Delete(List<string> guids)
        {
            string sql = "delete from [QT_Basic_Keyword] where [guid] in('" + string.Join("','", guids.ToArray()) + "')";
            int rval = new QT.DBO.OleDBHelper(myDB.connstring).ExecuteNonQuery(sql);
            return rval;
        }

        /// <summary>
        /// 检查基础关键字是否存在
        /// </summary>
        /// <param name="gid">分组ID</param>
        /// <param name="keyword">关键字</param>
        /// <returns>bool</returns>
        public static bool Exsits(string gid, string keyword)
        {
            string sql = "select [guid] from [QT_Basic_Keyword] where [keyword]='" + keyword + "'" + (string.IsNullOrEmpty(gid) ? "" : (" and [gid]='" + gid + "'"));
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);
            if (dt.Rows.Count > 0) return true;
            else return false;
        }

        /// <summary>
        /// 检查关键字检查结果是否已存在
        /// </summary>
        /// <param name="uid">网址ID</param>
        /// <param name="keyword">关键字</param>
        /// <param name="sid">搜索引擎ID</param>
        /// <returns>bool</returns>
        public static bool Exsits(string uid, string keyword, string sid)
        {
            string sql = "select [id] from [QT_Data_KeywordRank] where [uid]='" + uid + "' and [keyword]='" + keyword + "' and [sid]='" + sid + "'";
            DataTable dt = new QT.DBO.OleDBHelper(myDB.connstring).DataTable(sql);
            if (dt.Rows.Count > 0) return true;
            else return false;
        }
    }
}
